package com.lyq.bean.mySql;


import com.lyq.bean.Major;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class Major_Sql {

    /**
     * 获取所有专业列表
     * @return 专业列表
     */
    public static List<Major> getAllMajors() {
        List<Major> majors = new ArrayList<>();
        String sql = "SELECT major_id, major_name FROM majors ORDER BY major_id";

        try {
            (new MysqlLinking()).getLink();
            Connection conn = MysqlLinking.conn;
            PreparedStatement pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                Major major = new Major();
                major.setId(rs.getInt("major_id"));
                major.setName(rs.getString("major_name"));
                majors.add(major);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return majors;
    }

    /**
     * 根据ID获取专业
     * @param id 专业ID
     * @return 专业对象
     */
    public static Major getMajorById(int id) {
        Major major = null;
        String sql = "SELECT id, name FROM majors WHERE id = ?";

        try (Connection conn = MysqlLinking.conn;
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, id);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    major = new Major();
                    major.setId(rs.getInt("major_id"));
                    major.setName(rs.getString("major_name"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return major;
    }

    /**
     * 根据专业ID获取专业名称
     * @param id 专业ID
     * @return 专业名称
     */
    public static String getMajorNameById(int id) {
        String name = "";
        String sql = "SELECT name FROM majors WHERE id = ?";

        try (Connection conn = MysqlLinking.conn;
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, id);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    name = rs.getString("name");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return name;
    }
}
